package com.b511.swpu.cementbackend.repository;

import com.b511.swpu.cementbackend.entity.CM_WK_BASIC;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

public interface CM_WorkBasicRepository extends JpaRepository<CM_WK_BASIC, String>, JpaSpecificationExecutor<CM_WK_BASIC> {

    @Transactional
    CM_WK_BASIC findByWellNameAndCaseSize(String wellName, String caseSize);

    @Transactional
    List<CM_WK_BASIC> findWorkByWellName(String wellName);

    @Modifying
    @Query("update CM_WK_BASIC m set m.checkStatus='pass', m.checkPerson = :checkPerson where  m.wellName=:wellName and  m.caseSize = :caseSize")
    int updateCM_WK_BASICByWellNameAndCaseSize(@Param("wellName") String wellName,@Param("caseSize") String caseSize, @Param("checkPerson") String checkPerson);

    //以下四个查询为一个接口
    @Query(value = "select new CM_WK_BASIC(C.CM_WK_BASIC_ID,  C.wellName,  C.platformType,  C.operator,  C.contractor,  C.platform,  C.workType,  C.workDate,  C.reportDate,  C.holeSize,  C.caseSize,  C.checkStatus) from CM_WK_BASIC C  WHERE C.wellName like %:wellName% and C.caseSize = :caseSize and( C.checkStatus = 'is not pass' or C.checkStatus = 'is not check')")
    Page<CM_WK_BASIC> getCmWkBasicSomeNews(@Param("wellName") String wellName, @Param("caseSize") String caseSize,Pageable pageable);

    @Query(value = "select new CM_WK_BASIC(C.CM_WK_BASIC_ID,  C.wellName,  C.platformType,  C.operator,  C.contractor,  C.platform,  C.workType,  C.workDate,  C.reportDate,  C.holeSize,  C.caseSize,  C.checkStatus) from CM_WK_BASIC C WHERE C.checkStatus = 'is not pass' or C.checkStatus = 'is not check'")
    Page<CM_WK_BASIC> getCmWkBasicSomeNews(Pageable pageable);

    @Query(value = "select new CM_WK_BASIC(C.CM_WK_BASIC_ID,  C.wellName,  C.platformType,  C.operator,  C.contractor,  C.platform,  C.workType,  C.workDate,  C.reportDate,  C.holeSize,  C.caseSize,  C.checkStatus) from CM_WK_BASIC C  WHERE C.wellName like %:wellName% and( C.checkStatus = 'is not pass' or C.checkStatus = 'is not check')")
    Page<CM_WK_BASIC> getCmWkBasicSomeNewsByWellName(@Param("wellName") String wellName,Pageable pageable);

    @Query(value = "select new CM_WK_BASIC(C.CM_WK_BASIC_ID,  C.wellName,  C.platformType,  C.operator,  C.contractor,  C.platform,  C.workType,  C.workDate,  C.reportDate,  C.holeSize,  C.caseSize,  C.checkStatus) from CM_WK_BASIC C  WHERE  C.caseSize = :caseSize and( C.checkStatus = 'is not pass' or C.checkStatus = 'is not check')")
    Page<CM_WK_BASIC> getCmWkBasicSomeNewsByCaseSize(@Param("caseSize") String caseSize,Pageable pageable);


    //以下四个查询为一个接口
    @Query(value = "select new CM_WK_BASIC(C.CM_WK_BASIC_ID,  C.wellName,  C.platformType,  C.operator,  C.contractor,  C.platform,  C.workType,  C.workDate,  C.reportDate,  C.holeSize,  C.caseSize,  C.checkStatus) from CM_WK_BASIC C  WHERE C.wellName like %:wellName% and C.caseSize = :caseSize and C.uploadPerson = :uploadPerson ")
    Page<CM_WK_BASIC> getCmWkBasicByuploadPerson(@Param("uploadPerson") String uploadPerson,@Param("wellName") String wellName, @Param("caseSize") String caseSize,Pageable pageable);

    @Query(value = "select new CM_WK_BASIC(C.CM_WK_BASIC_ID,  C.wellName,  C.platformType,  C.operator,  C.contractor,  C.platform,  C.workType,  C.workDate,  C.reportDate,  C.holeSize,  C.caseSize,  C.checkStatus) from CM_WK_BASIC C WHERE  C.uploadPerson = :uploadPerson ")
    Page<CM_WK_BASIC> getCmWkBasicByuploadPerson(@Param("uploadPerson") String uploadPerson,Pageable pageable);

    @Query(value = "select new CM_WK_BASIC(C.CM_WK_BASIC_ID,  C.wellName,  C.platformType,  C.operator,  C.contractor,  C.platform,  C.workType,  C.workDate,  C.reportDate,  C.holeSize,  C.caseSize,  C.checkStatus) from CM_WK_BASIC C  WHERE C.wellName like %:wellName% and C.uploadPerson = :uploadPerson ")
    Page<CM_WK_BASIC> getCmWkBasicByuploadPersonAndWellName(@Param("uploadPerson") String uploadPerson,@Param("wellName") String wellName,Pageable pageable);

    @Query(value = "select new CM_WK_BASIC(C.CM_WK_BASIC_ID,  C.wellName,  C.platformType,  C.operator,  C.contractor,  C.platform,  C.workType,  C.workDate,  C.reportDate,  C.holeSize,  C.caseSize,  C.checkStatus) from CM_WK_BASIC C  WHERE  C.caseSize = :caseSize and C.uploadPerson = :uploadPerson ")
    Page<CM_WK_BASIC> getCmWkBasicByuploadPersonAndCaseSize(@Param("uploadPerson") String uploadPerson,@Param("caseSize") String caseSize,Pageable pageable);


    //以下四个查询为一个接口
    @Query(value = "select new CM_WK_BASIC(C.CM_WK_BASIC_ID,  C.wellName,  C.platformType,  C.operator,  C.contractor,  C.platform,  C.workType,  C.workDate,  C.reportDate,  C.holeSize,  C.caseSize,  C.checkStatus) from CM_WK_BASIC C  WHERE C.wellName like %:wellName% and C.caseSize = :caseSize and C.checkPerson = :checkPerson ")
    Page<CM_WK_BASIC> getCmWkBasicByCheckPerson(@Param("checkPerson") String checkPerson,@Param("wellName") String wellName, @Param("caseSize") String caseSize,Pageable pageable);

    @Query(value = "select new CM_WK_BASIC(C.CM_WK_BASIC_ID,  C.wellName,  C.platformType,  C.operator,  C.contractor,  C.platform,  C.workType,  C.workDate,  C.reportDate,  C.holeSize,  C.caseSize,  C.checkStatus) from CM_WK_BASIC C WHERE  C.checkPerson = :checkPerson ")
    Page<CM_WK_BASIC> getCmWkBasicByCheckPerson(@Param("checkPerson") String checkPerson,Pageable pageable);

    @Query(value = "select new CM_WK_BASIC(C.CM_WK_BASIC_ID,  C.wellName,  C.platformType,  C.operator,  C.contractor,  C.platform,  C.workType,  C.workDate,  C.reportDate,  C.holeSize,  C.caseSize,  C.checkStatus) from CM_WK_BASIC C  WHERE C.wellName like %:wellName% and C.checkPerson = :checkPerson ")
    Page<CM_WK_BASIC> getCmWkBasicByCheckPersonAndWellName(@Param("checkPerson") String checkPerson,@Param("wellName") String wellName,Pageable pageable);

    @Query(value = "select new CM_WK_BASIC(C.CM_WK_BASIC_ID,  C.wellName,  C.platformType,  C.operator,  C.contractor,  C.platform,  C.workType,  C.workDate,  C.reportDate,  C.holeSize,  C.caseSize,  C.checkStatus) from CM_WK_BASIC C  WHERE  C.caseSize = :caseSize and C.checkPerson = :checkPerson ")
    Page<CM_WK_BASIC> getCmWkBasicByCheckPersonAndCaseSize(@Param("checkPerson") String checkPerson,@Param("caseSize") String caseSize,Pageable pageable);

    @Query(value = "select count(*) from CM_WK_BASIC a left join BA_WELL b on a.wellname=b.wellname " +
            "where b.cementcompany =?1 and b.welltype=?2 " +
            "and work_date >= to_date(?3,'yyyy-mm-dd hh24:mi:ss') and report_date <= to_date(?4,'yyyy-mm-dd hh24:mi:ss')",nativeQuery = true)
    int getCountByCompanyAndWellType(@Param("cementcompany") String cementcompany,@Param("welltype") String welltype,@Param("work_date") String work_date,@Param("report_date") String report_date);

    @Query(value = "select count(*) from CM_WK_BASIC a left join BA_WELL b on a.wellname=b.wellname " +
            "where b.cementcompany =?1 and b.wellspacing=?2 " +
            "and work_date >= to_date(?3,'yyyy-mm-dd hh24:mi:ss') and report_date <= to_date(?4,'yyyy-mm-dd hh24:mi:ss')",nativeQuery = true)
    int getCountByCompanyAndWellSpacing(@Param("cementcompany") String cementcompany,@Param("welltype") String welltype,@Param("work_date") String work_date,@Param("report_date") String report_date);

    @Query(value = "select count(*) from CM_WK_BASIC a left join BA_WELL b on a.wellname=b.wellname " +
            "where b.cementcompany =?1 and a.case_size=?2 " +
            "and work_date >= to_date(?3,'yyyy-mm-dd hh24:mi:ss') and report_date <= to_date(?4,'yyyy-mm-dd hh24:mi:ss')",nativeQuery = true)
    int getCountByCompanyAndCaseSize(@Param("cementcompany") String cementcompany,@Param("case_size") String case_size,@Param("work_date") String work_date,@Param("report_date") String report_date);

    @Query(value = "select distinct case_size from CM_WK_BASIC ",nativeQuery = true)
    List<String> getCase_size();

    @Query(value = "select count(*) from CM_WK_BASIC where work_date > to_date(?1,'yyyy-mm-dd hh24:mi:ss')",nativeQuery = true)
    int test(@Param("work_date") String work_date);

    //下面三个方法是在没有传作业公司时使用
    @Query(value = "select count(*) from CM_WK_BASIC a left join BA_WELL b on a.wellname=b.wellname " +
            "where b.welltype=?1 " +
            "and work_date >= to_date(?2,'yyyy-mm-dd hh24:mi:ss') and report_date <= to_date(?3,'yyyy-mm-dd hh24:mi:ss')",nativeQuery = true)
    int getCountByWellType(@Param("welltype") String welltype,@Param("work_date") String work_date,@Param("report_date") String report_date);

    @Query(value = "select count(*) from CM_WK_BASIC a left join BA_WELL b on a.wellname=b.wellname " +
            "where b.wellspacing=?1 " +
            "and work_date >= to_date(?2,'yyyy-mm-dd hh24:mi:ss') and report_date <= to_date(?3,'yyyy-mm-dd hh24:mi:ss')",nativeQuery = true)
    int getCountByWellSpacing(@Param("welltype") String welltype,@Param("work_date") String work_date,@Param("report_date") String report_date);

    @Query(value = "select count(*) from CM_WK_BASIC a left join BA_WELL b on a.wellname=b.wellname " +
            "where a.case_size=?1 " +
            "and work_date >= to_date(?2,'yyyy-mm-dd hh24:mi:ss') and report_date <= to_date(?3,'yyyy-mm-dd hh24:mi:ss')",nativeQuery = true)
    int getCountByCaseSize(@Param("case_size") String case_size,@Param("work_date") String work_date,@Param("report_date") String report_date);
}
